package SQLx::Querylet;

use strict;
use warnings;

our $VERSION = '$Revision$';

use overload (
    '""' => \&stringify,
    '+'  => \&union,
    '*'  => \&union_all,
    '-'  => \&except,
    '|'  => \&expand,
    '&'  => \&contract,
);

use Carp qw( confess );
use Data::Dumper;
use DBI;
use Exporter qw( import );
use GraphViz;
use List::Util qw( max );
use SQLx::Querylet::Plugin;
use Statistics::Descriptive;

BEGIN {
    eval { require HTML::Generator };
    eval { use Lingua::EN::Inflect::Number qw( A to_S to_PL ) } or do {
        *A = sub { 'a(n) ' . $_[0] };
        *to_S = sub { $_[0] };
        *to_PL = sub { $_[0] };
    };
    eval { use Term::ANSIColor qw( color ) } or do {
        *color = sub { };
    };
};

our @EXPORT = qw( querylet );

sub querylet ($) {
    my $hr = shift;
    if (!eval { %$hr }) {
        $hr = { table => $hr };
    }
    eval { %$hr } or confess("Don't know how to make a querylet from " . A(ref($hr)));
    return bless $hr => __PACKAGE__;
}

sub study {
    my $self = shift;
    if (!$self->_get_dbh()) {
        confess("Can't study a querylet without a database specified");
    }
    my $dbh = $self->_connect();

    my $table_stats;
    my $column_stats;
    my $graph;

    for my $table (sort { length($a) <=> length($b) || $a cmp $b } $self->_get_all_tables()) {

        my $sql;
        my $sth;

        $graph->{ node }->{ $table } ||= {
            abbn => $self->_abbreviate(table => $table),
            full => $table,
        };

        $sql = "select count(*) as 'cardinality' from $table";
        $sth = $dbh->prepare($sql);
        $sth->execute();
        $table_stats->{ $table }->{ cardinality } = $sth->fetchrow_hashref()->{ cardinality };
        $sth->finish();

        for my $column ($self->_get_all_criteria_columns($table)) {

            $sql = "select ((n * x) / (x * x)) as selectivity from (
                select sum(n) as n, sum(x) as x from (
                    select count(distinct $column) as n, 0 as x from $table
                    union all
                    select 0 as n, count(*) as x from $table
                ) a
            ) b";
            $sth = $dbh->prepare($sql);
            $sth->execute();
            $column_stats->{ $table }->{ $column }->{ smart_selectivity } = $sth->fetchrow_hashref()->{ selectivity };
            $sth->finish();

            $sql = "select count(distinct $column) as 'distinct_count' from $table";
            $sth = $dbh->prepare($sql);
            $sth->execute();
            $column_stats->{ $table }->{ $column }->{ distinct_count } = $sth->fetchrow_hashref()->{ distinct_count };
            $sth->finish();

            $column_stats->{ $table }->{ $column }->{ distinct_ratio }
                = $column_stats->{ $table }->{ $column }->{ distinct_count }
                / $table_stats->{ $table }->{ cardinality }
            ;

            $sql = "select avg(num) as 'coverage' from (select $column, count(*) as num from $table group by $column) a";
            $sth = $dbh->prepare($sql);
            $column_stats->{ $table }->{ $column }->{ coverage } = $sth->fetchrow_hashref()->{ coverage };
            $sth->finish();

            $column_stats->{ $table }->{ $column }->{ naive_selectivity }
                = $column_stats->{ $table }->{ $column }->{ coverage }
                / $table_stats->{ $table }->{ cardinality }
            ;

            $graph->{ edges }->{ '~' }->{ $table } ||= [];
            push @{$graph->{ edges }->{ '~' }->{ $table }}, {
                criteria => [ $column, '=', '?' ],
                type => '~',
                naive_selectivity => $column_stats->{ $table }->{ $column }->{ naive_selectivity },
                smart_selectivity => $column_stats->{ $table }->{ $column }->{ smart_selectivity },
                ratio => $column_stats->{ $table }->{ $column }->{ distinct_ratio },
                count => $column_stats->{ $table }->{ $column }->{ distinct_count },
            };

        }

    }

    for my $join ($self->_get_all_joins()) {

        my $t1 = $join->{ t1 };
        my $t2 = $join->{ t2 };

        my $c1 = $t1.'.'.$join->{ c1 };
        my $c2 = $t2.'.'.$join->{ c2 };

        my $crit = $self->_get_join_criteria($join);

        my $downtype = uc $join->{ type };
        my $uptype
            = $downtype eq 'LEFT' ? 'RIGHT'
            : $downtype eq 'RIGHT' ? 'LEFT'
            : $downtype
            ;

        my $down = $self->_get_join_ratio($c1, $t1, $downtype, $t2, $crit);
        my $up   = $self->_get_join_ratio($c2, $t2, $uptype,   $t1, $crit);

        $graph->{ edges }->{ $t1 }->{ $t2 } ||= [];
        push @{$graph->{ edges }->{ $t1 }->{ $t2 }}, {
            criteria => $crit,
            type => $downtype,
            naive_selectivity => $column_stats->{ $t1 }->{ $join->{ c1 } }->{ naive_selectivity },
            smart_selectivity => $column_stats->{ $t1 }->{ $join->{ c1 } }->{ smart_selectivity },
            ratio => $down,
            count => $column_stats->{ $t1 }->{ $join->{ c1 } }->{ distinct_count },
        };

        $graph->{ edges }->{ $t2 }->{ $t1 } ||= [];
        push @{$graph->{ edges }->{ $t2 }->{ $t1 }}, {
            criteria => $crit,
            type => $uptype,
            naive_selectivity => $column_stats->{ $t2 }->{ $join->{ c2 } }->{ naive_selectivity },
            smart_selectivity => $column_stats->{ $t2 }->{ $join->{ c2 } }->{ smart_selectivity },
            ratio => $up,
            count => $column_stats->{ $t2 }->{ $join->{ c2 } }->{ distinct_count },
        };

        # TODO use the algorithm from the book to turn the above directed multigraph into a proper join order

    }

    return $self;
}

sub _get_join_criteria {
    my $self = shift;
    my ($join) = @_;
    if (!$join->{ c1 } or !$join->{ c2 }) {
        $join = $self->_build_join_criteria($join);
    }
    # TODO simple case first. More complicated cases TBD
    return $join->{ criteria } || ($join->{ t1 } . '.' . $join->{ c1 } . ' = ' . $join->{ t2 } . '.' . $join->{ c2 });
}

sub _get_join_ratio {
    my $self = shift;
    my ($pri_column, $pri_table, $join_type, $sec_table, $criteria) = @_;
    my $sql = "select avg(num) as 'ratio' from (select ${pri_column}, count(*) as num from ${pri_table} ${join_type} join ${sec_table} on (${criteria}) group by ${pri_column}) a";
    my $sth = $self->_connect()->prepare($sql);
    $sth->execute();
    my $ratio = $sth->fetchrow_hashref()->{ ratio };
    $sth->finish();
    return $ratio;
}

{
    my %abbn;
    my %expn;

    sub _abbreviate {
        my $self = shift;
        my ($type, $v) = map { uc $_ } @_;
        if (!$abbn{ $type }->{ $v }) {
            if (!$expn{ $type }->{ substr($v, 0, 1) }) {
                $abbn{ $type }->{ $v } = substr($v, 0, 1);
                $expn{ $type }->{ substr($v, 0, 1) } = $v;
            }
            elsif ($v =~ /_/) {
                my @a = split /_/, $v;
                my $x = max { length $_ } @a;
                for my $i (1 .. $x) {
                    my $try = join '', map { $_ =~ s/_//g; $_ } map { substr($_ . ('_' x $i - 1), 0, $i) } @a;
                    if (!$expn{ $type }->{ $try }) {
                        $abbn{ $type }->{ $v } = $try;
                        $expn{ $type }->{ $try } = $v;
                        last;
                    }
                }
            }
            else {
                for my $i (2 .. length $v) {
                    my $try = substr($v, 0, $i);
                    if (!$expn{ $type }->{ $try }) {
                        $abbn{ $type }->{ $v } = $try;
                        $expn{ $type }->{ $try } = $v;
                        last;
                    }
                }
            }
        }
        return $abbn{ $type }->{ $v };
    }

    sub _unabbreviate {
        my $self = shift;
        my ($type, $v) = map { uc $_ } @_;
        confess("Can't unabbreviate $type '$v', I've never seen it before") unless $expn{ $type }->{ $v };
        return $expn{ $type }->{ $v };
    }
}

{

    my $alias = 'A';
    sub _get_next_anonymous_alias {
        my $self = shift;
        my $key = $alias++;
        return "ANON_$key";
    }

}

sub stringify {
    my ($self) = @_;
    # TODO Here be draggones
    SQLx::Querylet::Plugin->register($self);
    return
        'SELECT '
        . ($self->{ fields } ? _grok_fields($self->{ fields }) : '*') . "\n"
        . 'FROM  ' . $self->_grok_tables() . "\n"
        . $self->{ where  } ? 'WHERE    ' . _grok_where(  $self->{ where  } ) . "\n" : ''
        . $self->{ group  } ? 'GROUP BY ' . _grok_group(  $self->{ group  } ) . "\n" : ''
        . $self->{ having } ? 'HAVING   ' . _grok_having( $self->{ having } ) . "\n" : ''
        . $self->{ order  } ? 'ORDER BY ' . _grok_order(  $self->{ order  } ) . "\n" : ''
        . ';'
        ;
}

sub _grok_tables {
    my $self = shift;
    my $table = $self->{ table };
    if (!ref($table)) {
        return $table . ' ' . $self->_abbreviate(table => $table);
    }
    if (ref($table) eq __PACKAGE__) {
        return '(' . $table . ') ' . __PACKAGE__->_get_next_anonymous_alias();
    }
    my @rv;
    my $lhs = $table[ 0 ];
    push @rv, $lhs . ' ' . $self->_abbreviate(table => $lhs);
    for my $i (1 .. $#$table) {
        my $join = $table[ $i ];
        if (ref($join) eq 'HASH') {
            push @rv,
                $join->{ type } . ' JOIN '
                . $join->{ table } . ' ' . $self->_abbreviate(table => $join->{ table })
                . ' ON ('
                    . $join->{ criteria } ? $join->{ criteria } : $self->_get_join_criteria({ t1 => ( ref($table[ $i - 1 ]) ? $table[ $i - 1 ]->{ table } : $table[ $i -1 ] ), t2 => $join->{ table } })
                . ')'
                ;
        }
        else {
            push @rv,
                'INNER JOIN '
                . $join . ' ' . $self->_abbreviate(table => $join)
                . ' ON ('
                    . $self->_get_join_criteria({ t1 => ( ref($table[ $i - 1 ]) ? $table[ $i - 1 ]->{ table } : $table[ $i -1 ] ), t2 => $join })
                . ')'
                ;
        }
    }
    return join ' ', @rv;
}

sub wrap {
    my $self = shift;
    my $class = ref($self);
    return $class->new({
        fields => '*',
        table => $self,
    });
}

sub union {
    my ($self, $other, $swapped) = @_;
    if ($swapped) {
        if (!ref($other) or ref($other) ne __PACKAGE__) {
            return "${other}${self}";
        }
        else {
            ($self, $other) = ($other, $self);
        }
    }
    my $oth = ref($other) eq __PACKAGE__ ? $other : querylet $other;
    if (!@{$oth->{ fields }}) {
        $oth->{ fields } = [ @{$self->{ fields }} ];
    }
    elsif (@{$oth->{ fields }} != @{$self->{ fields }}) {
        confess('Mismatched number of fields in UNION (' . scalar(@{$self->{ fields }) . ' vs ' . scalar(@{$oth->{ fields }}).')');
    }
    else {
        for my $i (0 .. $#{$self->{ fields }}) {
            my $sa = $self->{ fields }->[ $i ];
            my $oa = $oth->{  fields }->[ $i ];
            $sa =~ s/(?:.*\s+as\s+)?(.*)$/$1/i;
            $oa =~ s/(?:.*\s+as\s+)?(.*)$/$1/i;
            if ($sa ne $oa) {
                $oth->{ fields }->[ $i ] =~ s/(?:\s+as\s+).*$/ AS $sa/i;
            }
        }
    }
    if (!ref($self->{ table })) {
        $self->{ table } = [ $self->{ table } ];
    }
    push @{$self->{ table }}, (
        [ 'UNION' ],
        $oth,
    );
    return $self;
}

sub union_all {
    my ($self, $other, $swapped) = @_;
    my $oth = ref($other) eq __PACKAGE__ ? $other : querylet $other;
    if ($swapped) {
        ($self, $oth) = ($oth, $self);
    }
    if (!@{$oth->{ fields }}) {
        $oth->{ fields } = [ @{$self->{ fields }} ];
    }
    elsif (@{$oth->{ fields }} != @{$self->{ fields }}) {
        confess('Mismatched number of fields in UNION ALL (' . scalar(@{$self->{ fields }) . ' vs ' . scalar(@{$oth->{ fields }}).')');
    }
    else {
        for my $i (0 .. $#{$self->{ fields }}) {
            my $sa = $self->{ fields }->[ $i ];
            my $oa = $oth->{  fields }->[ $i ];
            $sa =~ s/(?:.*\s+as\s+)?(.*)$/$1/i;
            $oa =~ s/(?:.*\s+as\s+)?(.*)$/$1/i;
            if ($sa ne $oa) {
                $oth->{ fields }->[ $i ] =~ s/(?:\s+as\s+).*$/ AS $sa/i;
            }
        }
    }
    if (!ref($self->{ table })) {
        $self->{ table } = [ $self->{ table } ];
    }
    push @{$self->{ table }}, (
        [ 'UNION ALL' ],
        $oth,
    );
    return $self;
}

sub expand {
    my ($self, $other, $swapped) = @_;
    if ($swapped) {
        ($self, $other) = ($other, $self);
    }
    $self = ref($self) eq __PACKAGE__ ? $self : querylet $self;
    if (!ref($self->{ table })) {
        $self->{ table } = [ $self->{ table } ];
    }
    if ($other->{ table }) {
        if (!ref($self->{ table })) {
            $self->{ table } = [ $self->{ table } ];
        }
        push @{$self->{ table }}, (
            $swapped ? [ 'RIGHT OUTER JOIN' ] : [ 'LEFT OUTER JOIN' ],
            $other,
            $other->{ on } ? [ 'ON', $other->{ on } ]
            : $other->{ using } ? [ 'USING', $other->{ using } ]
            : _best_join_guess($self, $other, $swapped)
        );
    }
    if ($other->{ where }) {
        if ($self->{ where } and !eval { @{$self->{ where }} }) {
            $self->{ where } = [ $self->{ where } ];
        }
        if ($other->{ where } and !eval { @{$other->{ where }} }) {
            $other->{ where } = [ $other->{ where } ];
        }
        push @{$self->{ where }}, (
            'OR',
        ) if @{$self->{ where }};
        push @{$self->{ where }}, @{$other->{ where }};
    }
    if ($other->{ fields }) {
        if ($self->{ fields } and !eval { @{$self->{ fields }} }) {
            $self->{ fields } = [ $self->{ fields } ];
        }
        if ($other->{ fields } and !eval { @{$other->{ fields }} }) {
            $other->{ fields } = [ $other->{ fields } ];
        }
        push @{$self->{ fields }}, @{$other->{ fields }};
    }
    return $self;
}

sub contract {
    my ($self, $other, $swapped) = @_;
    if ($swapped) {
        ($self, $other) = ($other, $self);
    }
    $self = ref($self) eq __PACKAGE__ ? $self : querylet $self;
    if ($other->{ table }) {
        if (!ref($self->{ table })) {
            $self->{ table } = [ $self->{ table } ];
        }
        push @{$self->{ table }}, (
            [ 'INNER JOIN' ],
            $other,
            $other->{ on } ? [ 'ON', $other->{ on } ]
            : $other->{ using } ? [ 'USING', $other->{ using } ]
            : _best_join_guess($self, $other, $swapped)
        );
    }
    if ($other->{ where }) {
        if ($self->{ where } and !eval { @{$self->{ where }} }) {
            $self->{ where } = [ $self->{ where } ];
        }
        if ($other->{ where } and !eval { @{$other->{ where }} }) {
            $other->{ where } = [ $other->{ where } ];
        }
        push @{$self->{ where }}, (
            'AND',
        ) if @{$self->{ where }};
        push @{$self->{ where }}, @{$other->{ where }};
    }
    if ($other->{ fields }) {
        $self->{ fields } = $other->{ fields };
    }
    return $self;
}

sub except {
    my ($self, $other, $swapped) = @_;
    if ($swapped) {
        ($self, $other) = ($other, $self);
    }
    $self = ref($self) eq __PACKAGE__ ? $self : querylet $self;
    if ($other->{ where }) {
        if ($self->{ where } and !eval { @{$self->{ where }} }) {
            $self->{ where } = [ $self->{ where } ];
        }
        if (!@{$self->{ where }}) {
            push @{$self->{ where }}, '1';
        }
        push @{$self->{ where }}, (
            'AND NOT',
            $other->{ where },
        );
    }
    return $self;
}

sub column_stats {
    my $self = shift;
    my ($column, $table, $schema) = reverse map { split /\./, $_ } @_;
    my $dbh = $self->_get_dbh();
    my $stats = Statistics::Descriptive::Full->new();
    my $lookup;
    my $reverse_lookup;
    my $col_type = ($dbh->column_info($schema, $table, $column))[0]->{ DATA_TYPE };
    if ($col_type =~ /(char|text)/i) {
        my $sql = "SELECT DISTINCT $column FROM " . ($schema ? $shema . '.' : '') . "$table order by $column";
        my $sth = $dbh->prepare($sql);
        $sth->execute();
        my %hmap = ();
        my @hmap = ();
        while (my $row_ref = $sth->fetchrow_hashref()) {
            my $v = $row_ref->{ $column };
            push @hmap, $v;
            $hmap{ $v } = scalar @hmap;
        }
        $lookup = sub {
            my $data = shift;
            return $hmap{ $data };
        };
        $reverse_lookup = sub {
            my $encoded = shift;
            return $hmap[ int($encoded + 0.5) ];
        };
    }
    my $sql = "SELECT $column FROM " . ($schema ? $shema . '.' : '') . "$table";
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    my $i = 0;
    while (my $row_ref = $sth->fetchrow_hashref()) {
        my $v =$row_ref->{ $column };
        $stats->add($lookup ? $lookup->($v) : $v);
    }
    my $rv = {
        mode => $stats->mode(),
        mean => $stats->mean(),
        variance => $stats->variance(),
        skewness => $stats->skewness(),
        kurtosis => $stats->kurtosis(),
        quartiles => [
            $stats->min(),
            $stats->percentile(25),
            $stats->percentile(50),
            $stats->percentile(75),
            $stats->max(),
        ],
    };
    if ($lookup) {
        $rv->{ mean } = $reverse_lookup->($rv->{ mean });
        $rv->{ mode } = $reverse_lookup->($rv->{ mode });
        for (0 .. 4) {
            $rv->{ quartiles }->[ $_ ] = $reverse_lookup->($rv->{ quartiles }->[ $_ ]);
        }
    }
    return $rv;
}

1;

__END__

=head1 NAME SQLx::Querylet

=head1 VERSION $Revision$

=head1 SYNOPSIS

    use DBI;
    use SQLx::Querylet;

    sub display_query_results {
        my $sth = shift;
        $sth->execute();
        my $first_row = 1;
        while (my $row = $sth->fetchrow_hashref()) {
            if ($first_row) {
                print join(',', sort keys %$row) . "\n";
                $first_row = 0;
            }
            print join(',', @$row{ sort keys %$row }) . "\n";
        }
        return;
    }

    my $dbh = DBI->connect( ... );
    my $sth;

    my $customer_orders = querylet 'orders' | 'customers' | 'parts';
    $sth = $dbh->prepare($customer_orders);
    display_query_results($sth);

    my $is_widget = querylet { where => { part_name => [ 'like', '%widget%' ] } };
    my $widget_orders = $customer_orders & $is_widget;
    $sth = $dbh->prepare($widget_orders);
    display_query_results($sth);

    my $non_widget_orders = $customer_orders - $is_widget;
    $sth = $dbh->prepare($non_widget_orders);
    display_query_results($sth);

    my $is_thingy = querylet { where => { part_name => [ 'like'. '%thingy%' ] } };
    my $thingy_orders = $customer_orders & $is_thingy;
    $sth = $dbh->prepare($thingy_orders);
    display_query_results($sth);

    my $widget_or_thingy_orders = $widget_orders + $is_thingy;
    $sth = $dbh->prepare($widget_or_thingy_orders);
    display_query_results($sth);

    my $non_widget_or_thingy_orders = $customer_orders - ($is_widget + $is_thingy);
    $sth = $dbh->prepare($non_widget_or_thingy_orders);
    display_query_results($sth);

=head1 DESCRIPTION

The SQLx::Querylet module is an attempt to do for SQL what IO::All does for IO,
at least in part. Development is a continuously ongoing project, with features
and tweaks being added as and when they strike me, or are suggested (and found
to be feasible).

In essence, a querylet can be thought of as a "piece" of a full query. By applying
a small set of overloaded operators to a collection of querylets, quite complicated
SQL can be created (certainly more complicated than a human being might ordinarily
be expected to read and write).

A given querylet may specify one or more of the following:

    * A table name

    * A list of fields

    * Selection criteria

Selection criteria are specified in a manner as identical as possible to the
selection criteria syntax supported by DBIx::Abstract. See that module's documentation
before trying to understand how to use them.

To this end, a single function (the Class constructor) is exported...

=item querylet

Attempts to glark its single argument and turn it into a querylet.

=head1 OVERLOADED OPERATORS

=head2 EXPANSIVE OPERATORS

Operators are listed with the equivalent Object Method name after the slash.

=item . / union()

=item .. / union_all()

The . and .. operators are overloaded to provide UNION and UNION ALL respectively.

=item |

=item + / expand()

The | and + operators are aliases for the same operation. May be used to connect
tables, WHERE conditions or field lists with eachother. If used to connect tables,
a LEFT OUTER JOIN is issued. If used to connect WHERE conditions, they are connected
with OR. If used to connect field lists, the lists are concatenated.

By convention, | should be used to connect tables with tables, and + under any other
circumstances.

=head2 RESTRICTIVE OPERATORS

=item & / contract()

May be used to connect tables, WHERE conditions or field lists with eachother. If
used to connect tables, an INNER JOIN is issued. If used to connect WHERE conditions,
they are connected with AND. If used to connect field lists, field list from the
RHS is used.

=item - / except()

May be used to connect a table with a WHERE clause, or to connect two WHERE clauses.
The resultant WHERE clause is C<LHS> AND NOT C<RHS>.

Note that $foo - $bar - $baz is equivalent to $foo - ($bar + $baz).

=head1 OBJECT METHODS

=item study()

Makes an attempt to optimize the object (and therefore its generated SQL) by
studying some statistical information about the tables and indexes involved.

If no DBI connection has already been provided, study() requires that connection
criteria or an existing connection be provided as arguments.

Note that study() is a non-trivial operation, taking up to O(N ** 2) time, but
the resultant SQL may be many times faster and more efficient than that created
by using a raw, unstudied querylet, especially on complex multi-join queries. For
this reason, it is advisable to study() and save() any complex querylet that you
plan to use many times, and to load() it at application start time. The up-front
cost is higher, but for heavily-used SQL, the savings can be noticable.

=item advise()

Returns a mixture of SQL and "nearly plain English" containing advice for a DBA
on changes that could be made in an attempt to better optimize the database for
this query. For instance, the report highlights columns that appear to contain
unique values, but which are not UNIQUE or PRIMARY keys, indexes that are missing,
and other things that might help (or annoy).

If the querylet has not had study() called on it, advise() automatically calls it
in order to gather some of the data it relies upon.

=item explain()

Returns a visual representation of the query execution plan. If an argument is provided,
it must be something that IO::All can understand, and the execution plan will be written
to that handle instead. Also takes an optional hashref of options as its last argument.
The following switches are currently supported:

=over

=item format

Valid values of the format argument are:

=over

=item 'dumper'

A pure Data::Dumper dump of the raw guts of the execution plan. Probably of use
only during highly-arcane debugging.

=item 'text'

Outputs plain ASCII text.

=item 'colored'

Outputs plain ASCII text with embedded ANSI color codes for syntax highlighting.

=item 'pre'

Outputs a UTF-8 XHTML fragment, wrapped in a C<pre> tag, with syntax highlighting.

=item 'div'

Outputs a UTF-8 XHTML fragment, using C<div> and C<span> tags, with syntax highlighting.

=item 'gv'

Outputs a Graphviz object, with syntax highlighting. Node and edge placement will
have been performed in order to provide a similar layout to the other formats.

=item 'book'

Outputs a subset of the execution plan as a Graphviz object, in the layout expounded
by the book SQL Optimization, complete with the described statistical information.
The optimization plan followed by the study() method attempts to be an automation
of the methods described in that book. This explanation format is to allow you to
easily double-check that the most-optimal plan according to that book is in fact
the plan that is being generated.

=back

=item highlight

Only recognized for XHTML-based formats. May be set to /none/i, /css/i, /literal/i,
or /literal_dark/i, which controls whether syntax highlighting will be applied,
and if so, whether by means of CSS classes or hardcoded inline style attributes.
The default is 'literal'.

The 'literal' syntax highlighting assumes a white or pale neutral background. The
'literal_dark' syntax highlighting assumes a black or dark neutral background.

CSS class names TBD.

=item identify

Only recognized for XHTML-based formats. If not specified, or set to any false value,
XHTML elements will be emitted without any attempt to provide meaningful C<id> attributes.

If set to the number '1', C<id> attributes will be generated without a prefix string.

If set to any string, C<id> attributes will be generated using that string as a prefix.

If set to a coderef, that coderef must accept the raw C<id> tag, and return the C<id>
tag that should actually be used.

=back

=item load()

=item save()

Takes a single argument, being anything IO::All can understand, and loads or
saves a querylet, along with all associated state (such as the results of study()).

The load() method may also be used as a Class Method, in which case it acts as a
constructor.

=item dump()

Equivalent to save(), except more effort is taken to make the output human-readable.

The output is syntactically equivalent to the output from save(), except with some
additional whitespace. The load() method ignores most whitespace, and will understand
the output from dump() equivalently to the output from save().

=head2 DEFAULT JOIN CRITERIA

By default, if table $foo is to be joined to table $bar, and $bar has an C<id> column,
and $foo has a C<${bar}_id> column, those columns will be used. Note that $bar
in this case is plural-insensitive (at least in English). For example, the column
C<orders.customer_id> will automatically glom onto C<customers.id>, and so on.

Additionally, any column names that are verbatim identical between the two tables
will also be used, except for C<id>.

